<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/10/20
 * Time: 14:48
 */

namespace app\common\service;


use app\common\access\MyAccess;
use app\common\access\MyService;
use think\Db;

class Score extends MyService
{
    //读取督导列表
    function getList($page=1,$rows=20,$studentno = '%', $studentname = '%', $classno= '%', $school = '',$status='',$score='',$sort='studentno',$order='')
    {
        $result=['total'=>0,'rows'=>[]];
        $condition=null;
        if($studentno!='%') $condition['score.studentno']=array('like',$studentno);
        if($studentname!='%') $condition['score.studentname']=array('like',$studentname);
        if($school!='') $condition['score.school']= $school;
        if($classno!='%') $condition['score.classno']=array('like',$classno);
        if($status!='') $condition['score.status']= $status;
        if ($score != '') {
            if ($score == 1) $condition['score.score'] = '合格';
            if ($score == 2) $condition['score.score'] = '不合格';
        }
        $data=$this->query->table('score')
            ->page($page,$rows)
            ->field('studentno,rtrim(studentname) studentname,rtrim(statusname) statusname,classno,rtrim(classname) classname,school,rtrim(schoolname) schoolname,
            guidecom,guidevac,guidejob,link,rite,virtue,virtueb,skill,score')
            ->where($condition)->order($sort.' '.$order)->select();
        $count= $this->query->table('score')
            ->where($condition)->count();
        if(is_array($data)&&count($data)>0)
            $result=array('total'=>$count,'rows'=>$data);
        return $result;
    }
    //获取汇总
    function getSummary($classno='%'){
        MyAccess::checkAccess('R');
        $bind=array('CLASSNO'=>$classno);
        $result=['total'=>0,'rows'=>[]];
        $sql="SELECT SCHOOL,SCHOOLNAME,ISNULL(合格,0) PASS,ISNULL(不合格,0) FAIL
            FROM (
            SELECT SCHOOLNAME,SCHOOL,SCORE,COUNT(*) AS AMOUNT FROM SCORE
            WHERE SCORE.STATUS IN ('A','C','H') AND CLASSNO LIKE :CLASSNO
            GROUP BY SCHOOLNAME,SCHOOL,SCORE) AS S PIVOT(MAX(AMOUNT) FOR SCORE IN ([合格],[不合格])) T
            ORDER BY SCHOOL";
        $data=Db::query($sql,$bind);
        $count=count($data);
        if(is_array($data)&&count($data)>0)
            $result=array('total'=>$count,'rows'=>$data);
        return $result;
    }
}